package com.lc.dao.impl;

import com.lc.bean.Student;
import com.lc.dao.DBUtils;
import com.lc.dao.StudentDao;
import com.lc.util.StudentEnum;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @description:
 * @author: ManolinCoder
 * @time: 2021/8/22
 */
public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public List<Student> getStudents(String name,String stuno,int sex,int pageIndex,int pageSize) {
        ArrayList<Student> students = new ArrayList<>();//保存查询到的学生信息的list
        List params = new ArrayList();//保存sql参数的数组
        try {
            //这里需要使用StringBuffer来保存sql语句，方便sql语句的动态扩展（因为这里有多种情况）。
            //为什么要用where 1=1？因为如果直接拼接是select * from student and …………语法不正确需要where
            //而不能直接只写个where那样在没有拼接时就会语法错误，所以最后跟上一个恒等式来确保多种情况的语法都能正确。
            StringBuffer sql = new StringBuffer(" select * from student where 1=1 and state!=4 ");
            if (name!=null && name.length()>0){
                sql.append(" and stuname like ? ");//拼接时注意添加空格
                params.add("%"+name+"%");//模糊查询参数两侧别忘了百分号
            }
            if (stuno!=null && stuno.length()>0){
                sql.append(" and stuno=? ");//拼接时注意添加空格
                params.add(stuno);
            }
            if (sex!=-1){
                sql.append(" and sex=? ");//拼接时注意添加空格
                params.add(sex);
            }
            //分页（下标从0开始,比如当你想看第一页有五条数据时，应当是limit 0,5）
            sql.append(" limit ?,? ");//分页公式（limit (pageIndex-1)*pageSize,pageSize;）
            params.add((pageIndex-1)*pageSize);
            params.add(pageSize);

            resultSet = query(sql.toString(),params);
            if (resultSet == null){
                return null;
            }
            while (resultSet.next()){
                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegDate(resultSet.getDate("regdate"));
                students.add(student);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return students;
    }

    //获得数据总条数（基于模糊查询）
    @Override
    public int total(String name, String stuno, int sex) {
        List params = new ArrayList();//保存sql参数的数组
        int total = 0;
        try {
            //这里需要使用StringBuffer来保存sql语句，方便sql语句的动态扩展（因为这里有多种情况）。
            //为什么要用where 1=1？因为如果直接拼接是select * from student and …………语法不正确需要where
            //而不能直接只写个where那样在没有拼接时就会语法错误，所以最后跟上一个恒等式来确保多种情况的语法都能正确。
            StringBuffer sql = new StringBuffer(" select count(*) from student where 1=1 and state!=4 ");
            if (name != null && name.length() > 0) {
                sql.append(" and stuname like ? ");//拼接时注意添加空格
                params.add("%" + name + "%");//模糊查询参数两侧别忘了百分号
            }
            if (stuno != null && stuno.length() > 0) {
                sql.append(" and stuno=? ");//拼接时注意添加空格
                params.add(stuno);
            }
            if (sex != -1) {
                sql.append(" and sex=? ");//拼接时注意添加空格
                params.add(sex);
            }
            resultSet = query(sql.toString(), params);
            while (resultSet.next()){
                total = resultSet.getInt(1);//获取查询结果第一列的值,因为这里查count所以只有一列
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return total;
    }

    //新增学生
    @Override
    public int insertStu(Student student) {
        int i = 0;
        try {
            String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List params = new ArrayList();
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add (student.getEmail());
            params.add(student.getRegistered());
            params.add (student.getAddress());
            params.add (student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add (new Date());
            params.add(StudentEnum.READING.type);//1表示在读
            params.add(student.getIntroduction());
            params.add(student.getGid());
            i = update(sql,params);//返回受影响的行数
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return i;
    }

    //主键查询学生信息
    @Override
    public Student findById(int sid) {
        Student student = new Student();
        String sql = "select * from student where stuid=?";
        List params = new ArrayList();
        params.add(sid);
        resultSet = query(sql,params);
        try {
            if (resultSet == null){
                return null;
            }
            while (resultSet.next()){
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setEmail(resultSet.getString("email"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int updateStu(Student student) {
        int update = 0;
        try {
            String sql = "update student set stuname=?,stuno=?,sex=?,phone=?,email=?,registered=?,address=?,profession=?,idnumber=?,politics=?,regdate=?,state=?,introduction=?,gid=? where stuid=?";
            List params = new ArrayList();
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add (student.getEmail());
            params.add(student.getRegistered());
            params.add (student.getAddress());
            params.add (student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add (new Date());
            params.add(StudentEnum.READING.type);//1表示在读
            params.add(student.getIntroduction());
            params.add(student.getGid());
            params.add(student.getStuId());
            update = update(sql,params);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int delStu(String sid) {
        int update = 0;
        try{
            String sql = "update student set state=? where stuid=?";
            List params = new ArrayList();
            params.add(StudentEnum.DELETE.type);
            params.add(sid);
            update = update(sql, params);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return update;
    }
}
